#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from typing import List, NoReturn

from sqlalchemy.engine import ResultProxy
from sqlalchemy import  select, update,insert 
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload, noload
from sqlalchemy.sql import Select,text

from sqlalchemy import func
from loguru import logger


# from sqlalchemy.sql.functions  

# from backend.app.api import jwt
# from crud.base_crud import CRUDBase
# from model import Station,Img

# from backend.app.models import User, Role, Department
# from schema.img import  CreateImg, UpdateImg
# from app.schemas
from app.schemas.img import CVImg
from app.models import Img
from datetime import date, datetime, timedelta
import time
 

async def create_raw_img(db: AsyncSession, obj_in: CVImg) -> NoReturn:
    db_obj = Img(**obj_in.dict())
    db.add(db_obj)
    await db.commit()


# class CRUDEmp(CRUDBase[Img, CreateImg, UpdateImg]):
    # async def get_user_by_id(self, db: AsyncSession, user_id: int) -> User:
    #     return await self.get(db, user_id)

    # async def get_user_roles(self, db: AsyncSession, user_id: int) -> list:
    #     se = await db.execute(
    #         select(self.model)
    #         .where(self.model.id == user_id)
    #         .options(selectinload(self.model.roles))
    #     )
    #     role_ids = [r.id for r in se.scalars().first().roles]
    #     return role_ids

    # async def get_userinfo_by_id(self, db: AsyncSession, user_id: int) -> User:
    #     user = await db.execute(
    #         select(self.model)
    #         .options(selectinload(self.model.department))
    #         .options(noload(self.model.roles))
    #         .where(self.model.id == user_id)
    #     )
    #     return user.scalars().first()

    # async def create_raw_img(self, db: AsyncSession, obj_in: CreateImg) -> NoReturn:
    #     db_obj = self.model(**obj_in.dict())
    #     db.add(db_obj)
    #     await db.commit()
        # res = await db.execute(insert(self.model).values(obj_in.dict()))


    # async def get_img_by_timestamp(self, db: AsyncSession, timestamp: int) -> Img:
    #     img = await db.execute(select(self.model).where(self.model.created_at ==datetime.fromtimestamp(timestamp) ))
    #     return img.scalars().first()
    
    # async def get_imgs_by_dev_id(self, db: AsyncSession, id: int) -> List[Img]:
    #     img = await db.execute(select(self.model).where(self.model.dev_id == id ))
    #     return img.scalars().all()

    # async def get_imgs_by_dev_id_return_raw(self, db: AsyncSession, id: int) -> ResultProxy:
    #     res:ResultProxy = await db.execute(select(self.model).where(self.model.dev_id == id ))
    #     return res 






    # async def get_imgs_group_by_station_id(self, db: AsyncSession,content_type:str) -> List[Img]:
    #     if content_type == 'temp':
    #         textual_sql = text("""SELECT station.dev_id,station_id,content_temperature,max(datetime(created_at)),x,y FROM img INNER JOIN station WHERE img.station_id = station.id GROUP BY station_id;""")
    #         # textual_sql = textual_sql.columns(Station.dev_id,self.model.station_id,self.model.content_temperature,self.model.created_at,Station.x,Station.y)
    #     else:
    #         textual_sql = text("""SELECT station.dev_id,station_id,content_humidity,max(datetime(created_at)),x,y FROM img INNER JOIN station WHERE img.station_id = station.id GROUP BY station_id;""")
    #     res = await db.execute(textual_sql)

    #     #  将查询的结果转换成字典形式
    #     keys =list(res.keys())
    #     keys[3] = 'created_at'
    #     data = [dict(zip(keys, re)) for re in res]
    #     return data 


        # img = await db.execute(select(self.model.id,self.model.station_id,self.model.created_at).group_by(self.model.station_id,self.model.created_at))
        # first_date = datetime.now() - timedelta(days=1)
        # first_date = datetime.now() - timedelta(days=10)
        # last_date = datetime.now()
        # img = await db.execute((select(self.model.dev_id).where(self.model.created_at.between(first_date,last_date)).order_by(self.model.created_at)).group_by(self.model.station_id))
        # res = await db.execute(select(self.model.station_id).group_by(self.model.station_id))
        # res = await db.execute(text("select * from img"))



        #  获取一段时间的图片,返回一个list
        # first_date = datetime(2023, 2, 21,11,8,0)
        # last_date = datetime(2023, 2, 21,11,9,0)
        # img = await db.execute(select(self.model).where(self.model.created_at.between(first_date,last_date)  ))
        # return img.scalars().all()
 


    # async def get_station_by_id(self, db: AsyncSession, id: int) ->Station:
    #     db_obj = await db.execute(select(self.model).where(self.model.id == id))
    #     return db_obj.scalars().first()



    # async def get_user_by_username_with_roles(self, db: AsyncSession, username: str) -> User:
    #     user = await db.execute(
    #         select(self.model)
    #         .options(selectinload(self.model.roles))
    #         .where(self.model.username == username)
    #     )
    #     return user.scalars().first()

    # async def update_user_login_time(self, db: AsyncSession, username: str) -> int:
    #     user = await db.execute(
    #         update(self.model)
    #         .where(self.model.username == username)
    #         .values(last_login=func.now())
    #     )
    #     return user.rowcount

    # async def get_email_by_username(self, db: AsyncSession, username: str) -> str:
    #     user = await self.get_user_by_username(db, username)
    #     return user.email

    # async def get_username_by_email(self, db: AsyncSession, email: str) -> str:
    #     user = await db.execute(select(self.model).where(self.model.email == email))
    #     return user.scalars().first().username

    # async def get_avatar_by_username(self, db: AsyncSession, username: str) -> str:
    #     user = await self.get_user_by_username(db, username)
    #     return user.avatar

    # async def create_user(self, db: AsyncSession, user: CreateUser) -> NoReturn:
    #     user.password = jwt.get_hash_password(user.password)
    #     new_user = self.model(**user.dict(exclude={'role_id'}))
    #     roles_list = []
    #     for i in user.role_id:
    #         roles_list.append(await db.get(Role, i))
    #     new_user.roles = roles_list
    #     db.add(new_user)

    # async def update_userinfo(self, db: AsyncSession, current_user: User, obj: UpdateUser) -> int:
    #     dept = await db.get(Department, obj.department_id)
    #     obj.department_id = dept.id
    #     u = await db.execute(
    #         update(self.model)
    #         .where(self.model.id == current_user.id)
    #         .values(**obj.dict(exclude={'role_id'}))
    #     )
    #     # step1 删除所有角色
    #     for i in list(current_user.roles):
    #         current_user.roles.remove(i)
    #     # step2 添加新的角色
    #     role_list = []
    #     for i in obj.role_id:
    #         role_list.append(await db.get(Role, i))
    #     current_user.roles = role_list
    #     return u.rowcount

    # async def update_avatar(self, db: AsyncSession, current_user: User, avatar: str) -> int:
    #     return await self.update(db, current_user.id, {'avatar': avatar})

    # async def delete_user(self, db: AsyncSession, user_id: int) -> int:
    #     return await self.delete(db, user_id)

    # async def check_email(self, db: AsyncSession, email: str) -> User:
    #     mail = await db.execute(select(self.model).where(self.model.email == email))
    #     return mail.scalars().first()

    # async def delete_avatar(self, db: AsyncSession, user_id: int) -> int:
    #     return await self.update(db, user_id, {'avatar': None})

    # async def reset_password(self, db: AsyncSession, username: str, password: str) -> int:
    #     user = await db.execute(
    #         update(self.model)
    #         .where(self.model.username == username)
    #         .values(password=jwt.get_hash_password(password))
    #     )
    #     return user.rowcount

    # def get_users(self) -> Select:
    #     return select(self.model)\
    #         .order_by(self.model.time_joined.desc())\
    #         .options(selectinload(self.model.department))\
    #         .options(noload(self.model.roles))

    # async def get_user_is_super(self, db: AsyncSession, user_id: int) -> bool:
    #     user = await self.get_user_by_id(db, user_id)
    #     return user.is_superuser

    # async def get_user_is_active(self, db: AsyncSession, user_id: int) -> bool:
    #     user = await self.get_user_by_id(db, user_id)
    #     return user.is_active

    # async def super_set(self, db: AsyncSession, user_id: int) -> int:
    #     super_status = await self.get_user_is_super(db, user_id)
    #     user = await db.execute(
    #         update(User)
    #         .where(User.id == user_id)
    #         .values(is_superuser=False if super_status else True)
    #     )
    #     return user.rowcount


    # async def active_set(self, db: AsyncSession, user_id: int) -> int:
    #     active_status = await self.get_user_is_active(db, user_id)
    #     user = await db.execute(
    #         update(User)
    #         .where(User.id == user_id)
    #         .values(is_active=False if active_status else True)
    #     )
    #     return user.rowcount


# ImgDao: CRUDEmp = CRUDEmp(Img)
